import 'dart:convert';

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class User {
  final int? id;
  final String username;
  final String password;
  final String securityQuestion;
  final String securityAnswer;


  User({
    this.id,
    required this.username,
    required this.password,
    required this.securityQuestion,
    required this.securityAnswer,
  });

  // 转换为Map
  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'username': username,
      'password': password ,
      'securityQuestion': securityQuestion,
      'securityAnswer': securityAnswer,
    };
  }

  // 从Map创建FileInfo实例
  static User fromMap(Map<String, dynamic> map) {
    return User(
      id: map['id'],
      username: map['username'],
      password: map['password'],
      securityQuestion: map['securityQuestion'],
      securityAnswer: map['securityAnswer'],
    );
  }
}

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  factory DatabaseHelper() => _instance;

  DatabaseHelper._internal();

  static Database? _database;
  static const int _pageSize = 10; // 每页记录数

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB();
    return _database!;
  }

  Future<Database> _initDB() async {
    String path = join(await getDatabasesPath(), 'app_database.db');
    return await openDatabase(
      path,
      version: 1,
      onCreate: (Database db, int version) async {
        await db.execute("""
        CREATE TABLE users (
             id INTEGER PRIMARY KEY AUTOINCREMENT,
             username TEXT NOT NULL,
             password TEXT NOT NULL,
             securityQuestion TEXT NOT NULL,
             securityAnswer TEXT NOT NULL
          )
          """);

        // await db.execute("""
        // CREATE TABLE AspermHistoryRecord (
        //      id INTEGER PRIMARY KEY AUTOINCREMENT,
        //      username TEXT NOT NULL,
        //      avgTotalDisplacement INTEGER NOT NULL, -- 1表示true，0表示false
        //      avgVsl INTEGER NOT NULL, -- 1表示true，0表示false
        //      avgVcl INTEGER NOT NULL, -- 1表示true，0表示false
        //      avgVap INTEGER NOT NULL, -- 1表示true，0表示false
        //      avgAlh INTEGER NOT NULL, -- 1表示true，0表示false
        //
        //      numOfMotileTrajectories INTEGER,
        //      numOfDetectedSperms INTEGER,
        //      numOfALevelSperms INTEGER,
        //      numOfBLevelSperms INTEGER,
        //      numOfCLevelSperms INTEGER,
        //      createdTime INTEGER,
        //   )
        //   """);

        // 创建默认用户
        await db.insert('users', User(
          username: '3721',
          password: '3721',
          securityQuestion: '3721',
          securityAnswer: '3721',
        ).toMap());
      },
    );
  }

  Future<int> insertUser(User userInfo) async {
    Database db = await database;
    return await db.insert('users', userInfo.toMap(), conflictAlgorithm: ConflictAlgorithm.replace);
  }



  Future<int> deleteUser(int id) async {
    Database db = await database;
    return await db.delete(
      'users',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // 查询所有用户
  Future<List<User>> queryAllUsers() async {
    Database db = await database;
    List<Map<String, dynamic>> maps = await db.query('users');
    return maps.map((map) => User.fromMap(map)).toList();
  }

  // 更新用户信息
  Future<int> updateUser(User user) async {
    Database db = await database;
    return await db.update(
      'users',
      user.toMap(),
      where: 'id = ?',
      whereArgs: [user.id],
    );
  }

  // 根据用户名查询用户
  Future<User?> queryUserByUsername(String username) async {
    Database db = await database;
    List<Map<String, dynamic>> maps = await db.query(
      'users',
      where: 'username = ?',
      whereArgs: [username],
    );
    if (maps.isNotEmpty) {
      return User.fromMap(maps.first);
    }
    return null;
  }
  // 检查用户名是否已存在
  Future<bool> isUsernameExists(String username) async {
    User? user = await queryUserByUsername(username);
    return user != null;
  }

}
